package com.dao.imp;

import com.dao.UserDao;
import com.entity.Admin;
import com.entity.Company;
import com.entity.User;
import com.mysql.cj.util.StringUtils;
import com.utils.DButil;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDaoimpl implements UserDao {


    /**
     * 该方法用来检测用户是否存在
     * @param name 用户名
     * @param pwd 密码
     * @return
     */
    @Override
    public Admin selectAllAdmin(String name, String pwd) {
        Connection conn = DButil.getConn();
        String sql="SELECT * from r_admin WHERE name=? and pwd=?";
        try {
            PreparedStatement ps= conn.prepareStatement(sql);
            ps.setString(1,name);
            ps.setString(2,pwd);
            ResultSet rs = ps.executeQuery();
            System.out.println("name = " + name);
            System.out.println("pwd = " + pwd);
            Admin admin = new Admin();


            while(rs.next()){
               String name1 = rs.getString("name");
               String pwd1 = rs.getString("pwd");
               admin.setName(name1);
               admin.setPwd(pwd1);
               return admin;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public User selectAllUser(String name, String pwd) {
        Connection conn = DButil.getConn();
        String sql="SELECT * from r_user,r_userinfo WHERE r_user.userid=? and pwd=? and r_user.userid=r_userinfo.userid";
        try {
            PreparedStatement ps= conn.prepareStatement(sql);
            ps.setString(1,name);
            ps.setString(2,pwd);
            ResultSet rs = ps.executeQuery();
            User user = new User();

            while (rs.next()) {
                String state = rs.getString("state");
                if("正常用户".equals(state)) {
                    String name1 = rs.getString("name");
                    String pwd1 = rs.getString("pwd");
                    String userId = rs.getString("userid");
                    String state1 = rs.getString("state");
                    String realname = rs.getString("realname");
                    String usex = rs.getString("usex");
                    String age = rs.getString("age");
                    String school = rs.getString("school");
                    String xueli = rs.getString("xueli");
                    String cardnum = rs.getString("cardnum");
                    String zhuanye = rs.getString("zhuanye");
                    String description = rs.getString("description");
                    String photo = rs.getString("photo");
                    String tel = rs.getString("tel");

                    user.setUserId(userId);
                    user.setName(name1);
                    user.setPwd(pwd1);
                    user.setState(state1);
                    user.setRealName(realname);
                    user.setuSex(usex);
                    user.setAge(age);
                    user.setSchool(school);
                    user.setXueLi(xueli);
                    user.setCardNum(cardnum);
                    user.setMajor(zhuanye);
                    user.setDescription(description);
                    user.setPhoto(photo);
                    user.setTel(tel);
                    return user;
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public Company selectAllCompany(String name, String pwd) {
        Connection conn = DButil.getConn();
        String sql="SELECT * from r_company,r_companyinfo WHERE r_company.companyid=? and pwd=? and r_company.companyid=r_companyinfo.companyid";
        try {
            PreparedStatement ps= conn.prepareStatement(sql);
            ps.setString(1,name);
            ps.setString(2,pwd);
            ResultSet rs = ps.executeQuery();
            Company company = new Company();


                while (rs.next()) {
                    String state = rs.getString("state");
                    if("正常用户".equals(state)) {
                        String name1 = rs.getString("name");
                        String pwd1 = rs.getString("pwd");
                        String companyId = rs.getString("companyid");
                        String state1 = rs.getString("state");
                        String companyname = rs.getString("companyname");
                        String companyaddress = rs.getString("companyaddress");
                        String companytel = rs.getString("companytel");
                        String companyemail = rs.getString("companyemail");
                        String companydescription = rs.getString("companydescription");
                        String addtime = rs.getString("addtime");
                        String gongshangnum = rs.getString("gongshangnum");
                        company.setCompanyId(companyId);
                        company.setPwd(pwd1);
                        company.setCompanyName(name1);
                        company.setState(state1);
                        company.setCompanyName(companyname);
                        company.setCompanyAddress(companyaddress);
                        company.setCompanyTel(companytel);
                        company.setCompanyEmail(companyemail);
                        company.setCompanyDescription(companydescription);
                        company.setAddTime(addtime);
                        company.setGongNum(gongshangnum);
                        return company;
                    }
                }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 用于实现获取企业表的所有信息
     * @return
     */
    @Override
    public List<Object> selectCompanyList() {
        Connection conn = DButil.getConn();
        String sql="SELECT * from r_companyinfo";
        List<Object> companys = new ArrayList<Object>();
        try {
            PreparedStatement ps=   conn.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();

            while(rs.next()){
                Company company = new Company();
                company.setCompanyId(rs.getString("companyid"));
                company.setCompanyName(rs.getString("companyname"));
                company.setCompanyAddress(rs.getString("companyaddress"));
                company.setCompanyTel(rs.getString("companytel"));
                company.setCompanyEmail(rs.getString("companyemail"));
                company.setCompanyDescription(rs.getString("companydescription"));
                company.setAddTime(rs.getString("addtime"));
                company.setGongNum(rs.getString("gongshangnum"));
                company.setState(rs.getString("state"));
                System.out.println("company = " + company);
                companys.add(company);
            }
            return companys;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 获取企业列表 实现分页
     * @param page 页数 mysql 会从0开始计算
     * @param limit 限制页数
     * @return
     */
    @Override
    public List<Object> selectCompanyList(String page, String limit) {
        Connection conn = DButil.getConn();
        String sql="SELECT * from r_companyinfo limit ?,?";
        int page1 = Integer.parseInt(page);
        int limit1 = Integer.parseInt(limit);
        List<Object> companys = new ArrayList<Object>();

        try {
            PreparedStatement ps=   conn.prepareStatement(sql);
            ps.setInt(1,(page1-1)*limit1);
            ps.setInt(2,limit1);
            ResultSet rs = ps.executeQuery();

            while(rs.next()){
                Company company = new Company();
                company.setCompanyId(rs.getString("companyid"));
                company.setCompanyName(rs.getString("companyname"));
                company.setCompanyAddress(rs.getString("companyaddress"));
                company.setCompanyTel(rs.getString("companytel"));
                company.setCompanyEmail(rs.getString("companyemail"));
                company.setCompanyDescription(rs.getString("companydescription"));
                company.setAddTime(rs.getString("addtime"));
                company.setGongNum(rs.getString("gongshangnum"));
                company.setState(rs.getString("state"));
//                System.out.println("company = " + company);
                companys.add(company);
            }
            return companys;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<Object> selectCompanyList(HttpServletRequest req, HttpServletResponse resp) throws SQLException, ServletException, IOException {
        Connection conn = DButil.getConn();
        String pageStr = req.getParameter("page");
        System.out.println("pageStr = " + pageStr);
        String limitStr = req.getParameter("limit");
        System.out.println("limitStr = " + limitStr);
        String keyCompanyName = req.getParameter("keyCompanyName");
        String keyGongNum = req.getParameter("keyGongNum");
        String keyId = req.getParameter("keyId");
        String keyState = req.getParameter("keyState");
        int page1 = Integer.parseInt(pageStr);
        int limit1 = Integer.parseInt(limitStr);
        String sql = "";
        PreparedStatement ps = null;
        if (StringUtils.isNullOrEmpty(keyCompanyName) && StringUtils.isNullOrEmpty(keyGongNum) &&
                StringUtils.isNullOrEmpty(keyId) && StringUtils.isNullOrEmpty(keyState)) {
            sql = "SELECT * from r_companyinfo limit ?,?";

            ps = conn.prepareStatement(sql);
            ps.setInt(1, (page1 - 1) * limit1);
            ps.setInt(2, limit1);
            System.out.println("sql = " + sql);
        } else if (!StringUtils.isNullOrEmpty(keyState)||!StringUtils.isNullOrEmpty(keyId)||!StringUtils.isNullOrEmpty(keyGongNum)||!StringUtils.isNullOrEmpty(keyCompanyName)){
            sql = "SELECT * from r_companyinfo where state like ? and  companyid like ? and gongshangnum like ? and companyname like ? limit ?,?";
            System.out.println("sql = " + sql);
            ps = conn.prepareStatement(sql);
            ps.setInt(5, (page1 - 1) * limit1);
            ps.setInt(6, limit1);
            ps.setString(1, "%"+keyState+"%");
            ps.setString(2, "%"+keyId+"%");
            ps.setString(3, "%"+keyGongNum+"%");
            ps.setString(4, "%"+keyCompanyName+"%");
        }
        ResultSet rs = ps.executeQuery();
        List<Object> companys = new ArrayList<Object>();
        while (rs.next()) {
            Company company = new Company();
            company.setCompanyId(rs.getString("companyid"));
            company.setCompanyName(rs.getString("companyname"));
            company.setCompanyAddress(rs.getString("companyaddress"));
            company.setCompanyTel(rs.getString("companytel"));
            company.setCompanyEmail(rs.getString("companyemail"));
            company.setCompanyDescription(rs.getString("companydescription"));
            company.setAddTime(rs.getString("addtime"));
            company.setGongNum(rs.getString("gongshangnum"));
            company.setState(rs.getString("state"));
//                System.out.println("company = " + company);
            companys.add(company);
        }
        return companys;

    }

    @Override
    public List<Object> selectAllCompanyList(HttpServletRequest req, HttpServletResponse resp) throws SQLException, ServletException, IOException {
        Connection conn = DButil.getConn();
//        String pageStr = req.getParameter("page");
//        System.out.println("pageStr = " + pageStr);
//        String limitStr = req.getParameter("limit");
//        System.out.println("limitStr = " + limitStr);
        String keyCompanyName = req.getParameter("keyCompanyName");
        String keyGongNum = req.getParameter("keyGongNum");
        String keyId = req.getParameter("keyId");
        String keyState = req.getParameter("keyState");
        System.out.println("keyState = " + keyState);
        String sql = "";
        PreparedStatement ps = null;
        if (StringUtils.isNullOrEmpty(keyCompanyName) && StringUtils.isNullOrEmpty(keyGongNum) &&
                StringUtils.isNullOrEmpty(keyId) && StringUtils.isNullOrEmpty(keyState)) {
            sql = "SELECT * from r_companyinfo";

            ps = conn.prepareStatement(sql);

            System.out.println("sql = " + sql);
        } else if (!StringUtils.isNullOrEmpty(keyState)||!StringUtils.isNullOrEmpty(keyId)||!StringUtils.isNullOrEmpty(keyGongNum)||!StringUtils.isNullOrEmpty(keyCompanyName)){
            sql = "SELECT * from r_companyinfo where state like ? and  companyid like ? and gongshangnum like ? and companyname like ?";
            System.out.println("sql = " + sql);
            ps = conn.prepareStatement(sql);
            ps.setString(1, "%"+keyState+"%");
            ps.setString(2, "%"+keyId+"%");
            ps.setString(3, "%"+keyGongNum+"%");
            ps.setString(4, "%"+keyCompanyName+"%");
        }
        ResultSet rs = ps.executeQuery();
        List<Object> companys = new ArrayList<Object>();
        while (rs.next()) {
            Company company = new Company();
            company.setCompanyId(rs.getString("companyid"));
            company.setCompanyName(rs.getString("companyname"));
            company.setCompanyAddress(rs.getString("companyaddress"));
            company.setCompanyTel(rs.getString("companytel"));
            company.setCompanyEmail(rs.getString("companyemail"));
            company.setCompanyDescription(rs.getString("companydescription"));
            company.setAddTime(rs.getString("addtime"));
            company.setGongNum(rs.getString("gongshangnum"));
            company.setState(rs.getString("state"));
            companys.add(company);
        }
        return companys;

    }

    @Override
    public List<Object> selectUserList(HttpServletRequest req, HttpServletResponse resp) throws SQLException, ServletException, IOException {
        Connection conn = DButil.getConn();
        String pageStr = req.getParameter("page");
        System.out.println("pageStr = " + pageStr);
        String limitStr = req.getParameter("limit");
        System.out.println("limitStr = " + limitStr);
        String keyName = req.getParameter("keyName");
        String keyId = req.getParameter("keyId");
        String keyState = req.getParameter("keyState");
        int page1 = Integer.parseInt(pageStr);
        int limit1 = Integer.parseInt(limitStr);
        String sql = "";
        PreparedStatement ps = null;
        if (StringUtils.isNullOrEmpty(keyName) &&
                StringUtils.isNullOrEmpty(keyId) && StringUtils.isNullOrEmpty(keyState)) {
            sql = "SELECT * from r_userinfo limit ?,?";

            ps = conn.prepareStatement(sql);
            ps.setInt(1, (page1 - 1) * limit1);
            ps.setInt(2, limit1);
            System.out.println("sql = " + sql);
        } else if (!StringUtils.isNullOrEmpty(keyState)||!StringUtils.isNullOrEmpty(keyId)||!StringUtils.isNullOrEmpty(keyName)) {
            sql = "SELECT * from r_userinfo where state like ? and userid like ?  and uname like ? limit ?,?";
            System.out.println("sql = " + sql);
            ps = conn.prepareStatement(sql);
            ps.setInt(4, (page1 - 1) * limit1);
            ps.setInt(5, limit1);
            ps.setString(1, "%"+keyState+"%");
            ps.setString(2, "%"+keyId+"%");
            ps.setString(3, "%"+keyName+"%");
        }
        ResultSet rs = ps.executeQuery();
        List<Object> users = new ArrayList<Object>();
        while (rs.next()) {
            User user = new User();
            user.setUserId(rs.getString("userid"));
            user.setName(rs.getString("uname"));
            user.setRealName(rs.getString("realname"));
            user.setuSex(rs.getString("usex"));
            user.setAge(rs.getString("age"));
            user.setSchool(rs.getString("school"));
            user.setXueLi(rs.getString("xueli"));
            user.setCardNum(rs.getString("cardnum"));
            user.setMajor(rs.getString("zhuanye"));
            user.setTel(rs.getString("tel"));
            user.setState(rs.getString("state"));

            users.add(user);
        }
        return users;
    }

    /**
     *计算企业的个数，传递给Vo类的count 从而实现分页
     * @return
     * @throws SQLException
     */
    @Override
    public int countCompany() throws SQLException {
        Connection conn = DButil.getConn();
        String sql="select count(*) as sum from r_companyinfo";
        PreparedStatement ps=   conn.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        while (rs.next()){
            return rs.getInt("sum");
        }
        return 0;
    }

    @Override
    public int countCompany(HttpServletRequest req, HttpServletResponse resp) throws SQLException, ServletException, IOException {
        Connection conn = DButil.getConn();
        String keyCompanyName = req.getParameter("keyCompanyName");
        String keyGongNum = req.getParameter("keyGongNum");
        String keyId = req.getParameter("keyId");
        String keyState = req.getParameter("keyState");

        String sql = "";
        PreparedStatement ps = null;
        if (StringUtils.isNullOrEmpty(keyCompanyName) && StringUtils.isNullOrEmpty(keyGongNum) &&
                StringUtils.isNullOrEmpty(keyId) && StringUtils.isNullOrEmpty(keyState)) {
            sql = "SELECT count(*) as sum from r_companyinfo";
            ps = conn.prepareStatement(sql);
            System.out.println("sql = " + sql);
        }
        else if (!StringUtils.isNullOrEmpty(keyState)||!StringUtils.isNullOrEmpty(keyId)||!StringUtils.isNullOrEmpty(keyGongNum)||!StringUtils.isNullOrEmpty(keyCompanyName)){
            sql = "SELECT count(*) as sum from r_companyinfo where state like ? and companyid like ? and gongshangnum like ? and companyname like ?";
            System.out.println("sql = " + sql);
            ps = conn.prepareStatement(sql);
            ps.setString(1, "%"+keyState+"%");
            ps.setString(2, "%"+keyId+"%");
            ps.setString(3, "%"+keyGongNum+"%");
            ps.setString(4, "%"+keyCompanyName+"%");
        }
        ResultSet rs = ps.executeQuery();
        while (rs.next()){
            return rs.getInt("sum");
        }
        return 0;
    }

    @Override
    public int countUser(HttpServletRequest req, HttpServletResponse resp) throws SQLException, ServletException, IOException {
        Connection conn = DButil.getConn();
        String keyName = req.getParameter("keyName");
        String keyId = req.getParameter("keyId");
        String keyState = req.getParameter("keyState");

        String sql = "";
        PreparedStatement ps = null;
        if (StringUtils.isNullOrEmpty(keyName) &&
                StringUtils.isNullOrEmpty(keyId) && StringUtils.isNullOrEmpty(keyState)) {
            sql = "SELECT count(*) as sum from r_userinfo";
            ps = conn.prepareStatement(sql);
            System.out.println("sql = " + sql);
        }
//        else if (!StringUtils.isNullOrEmpty(keyName)) {
//            sql = "SELECT  count(*) as sum from r_userinfo where uname like ?";
//            ps = conn.prepareStatement(sql);
//            ps.setString(1, "%"+keyName+"%");
//            System.out.println("ps = " + ps);
//        } else if (!StringUtils.isNullOrEmpty(keyId)) {
//            sql = "SELECT count(*) as sum from r_userinfo where userid like ? ";
//            System.out.println("sql = " + sql);
//            ps = conn.prepareStatement(sql);
//            ps.setString(1, "%"+keyId+"%");
//        }
        else if (!StringUtils.isNullOrEmpty(keyState)||!StringUtils.isNullOrEmpty(keyId)||!StringUtils.isNullOrEmpty(keyName)) {
            sql = "SELECT count(*) as sum from r_userinfo where state like ? and  userid like ? and uname like ?";
            System.out.println("sql = " + sql);
            ps = conn.prepareStatement(sql);
            ps.setString(1, "%"+keyState+"%");
            ps.setString(2, "%"+keyId+"%");
            ps.setString(3, "%"+keyName+"%");
        }
        ResultSet rs = ps.executeQuery();
        while (rs.next()){
            return rs.getInt("sum");
        }
        return 0;
    }

    @Override
    public int addUser(User user) {
        Connection conn = DButil.getConn();
        String sql = "insert into r_user(userid,pwd,name,state) values(?,?,?,'正常用户')";
        try {
            PreparedStatement ps= conn.prepareStatement(sql);
            ps.setString(1,user.getUserId());
            ps.setString(2,user.getPwd());
            ps.setString(3,user.getName());
            System.out.println("ps = " + ps);
            int i = ps.executeUpdate();

           if(i==1){
               String sql2 = "insert into r_userinfo(uname,userid,state) values(?,?,'正常用户')";
               ps= conn.prepareStatement(sql2);
               ps.setString(1,user.getName());
               ps.setString(2,user.getUserId());
               int i1 = ps.executeUpdate();
               System.out.println("i1 = " + i1);
               if(i1==1){
                   return 1;
               }else{
                   return 0;
               }

           }else{
               return 0;
           }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }


    @Override
    public int addCompany(Company company) {
        Connection conn = DButil.getConn();
        String sql = "insert into r_company(companyid,pwd,name,state) values(?,?,?,'正常用户')";
        try {
            PreparedStatement ps= conn.prepareStatement(sql);
            ps.setString(1,company.getCompanyId());
            ps.setString(2,company.getPwd());
            ps.setString(3,company.getCompanyName());
            System.out.println("addCompanyps = " + ps);
            int i = ps.executeUpdate();
            System.out.println("i = " + i);

            if(i==1){
                String sql2 = "insert into r_companyinfo(companyname,companyid,state) values(?,?,'正常用户')";
                ps= conn.prepareStatement(sql2);
                ps.setString(1,company.getCompanyName());
                ps.setString(2,company.getCompanyId());
                System.out.println("addCompanysql2 = " + sql2);
                int i1 = ps.executeUpdate();
                System.out.println("i4 = " + i1);
                if(i1==1){
                    return 1;
                }else{
                    return 0;
                }

            }else{
                return 0;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }


}
